package com.guet.niit.Admin.dao;

import com.guet.niit.Admin.pojo.User;
import com.guet.niit.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;

public class UserDaolmpl {

    /**
     * 用户添加
     * 1.SQL: "INSERT INTO common_user (id,name,password) "+
     *                     "VALUES("
     *                     + user.getId()
     *                     + ",'"
     *                     + user.getName()
     *                     + "','"
     *                     + user.getPhone()
     *                     + "')";
     *
     * @return
     */
    public boolean userAdd(User user){

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtil.createConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql = "INSERT INTO common_user (id,username,password) "+
                    "VALUES("
                    + user.getId()
                    + ",'"
                    + user.getUsername()
                    + "','"
                    + user.getPassword()
                    + "')";
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn,stmt,rs);
        }
        return false;

    }


    /**
     * 用户删除
     * 1.SQL: "DELETE FROM common_user WHERE id=" + id;
     * @return
     */
    public boolean userDelete(Long id){
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            connection = JDBCUtil.createConnection();
            // 获得Statement对象
            statement = connection.createStatement();
            // 发送SQL语句
            String sql = "DELETE FROM common_user WHERE id=" + id;
            int num = statement.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }
        return false;
    }


    /**
     * 用户信息修改
     * 1.SQL:"UPDATE common_user set name='" + user.getName()
     *                     + "',phone='" + user.getPassword()
     *                     + "' WHERE id=" + user.getId();
     *
     * @return
     */
    public boolean userModify(String username ,Long id, String password){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtil.createConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            User user = new User(username,id,password);
            String sql =  "UPDATE common_user set username='" + user.getUsername()
                    + "',password='" + user.getPassword()
                    + "' WHERE id=" + user.getId();

            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, stmt,rs );
        }
        return false;

    }



    /**
     * 用户查询
     * 1.SQl:select * from common_user;
     * 2.结果：List<User>
     */
    public ArrayList<User> userInquire() {
        Connection connection = null;
        try {
            connection = JDBCUtil.createConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Statement statement = null;
        ResultSet rs = null;
        User user = null;
        ArrayList<User> arrayList;
        try {

            PreparedStatement preparedStatement = null;
            String sql = "select * from common_user";
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            arrayList = new ArrayList<>();
            while (rs.next()) {
                String username = rs.getString("username");
                Long id = rs.getLong("id");
//                Long password = rs.getLong("password");
                String password = rs.getString("password");
                user = new User(username, id, password);
                arrayList.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }

        return arrayList;

    }
}
